﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using ConsumeSys.Orm;
using System.Data.Sql;
using System.Data.SqlClient;
using ConsumeSys.Model;

namespace ConsumeSys.DAL
{
    /// <summary>
    /// 消费DAL
    /// </summary>
    public class ConsumeDAL : ConsumeSys.Common.DisposablClass
    {
        /// <summary>
        /// 用户类
        /// </summary>
        private UserDAL userDAL = new UserDAL();

        #region 消费类型

        /// <summary>
        /// 添加消费类型
        /// </summary>
        /// <param name="consumeType">需要添加的实体</param>
        /// <returns>受影响的行数</returns>
        public int AddConsumeType(Model.ConsumeType consumeType)
        {
            #region Insert语句

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("INSERT INTO [dbo].[tb_ConsumeType]");
            builder.AppendFormat("([ct_TypeName]");
            builder.AppendFormat(",[ct_TypeAbbreviation]");
            builder.AppendFormat(",[ct_ParentId]");
            builder.AppendFormat(",[ct_Remark]");
            builder.AppendFormat(",[ct_UpdateTime])");
            builder.AppendFormat(" VALUES ");
            builder.AppendFormat("('{0}'", consumeType.ct_TypeName);
            builder.AppendFormat(",'{0}'", consumeType.ct_TypeAbbreviation);
            builder.AppendFormat(",{0}", consumeType.ct_ParentId);
            builder.AppendFormat(",'{0}'", consumeType.ct_Remark);
            builder.AppendFormat(",getdate())");

            #endregion
            int count = DBHelper.SQLHepler.ExecuteSql(builder.ToString());
            return count;
        }

        /// <summary>
        /// 返回单个消费类型
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>ConsumeType</returns>
        public Model.ConsumeType GetConsumeTypeModel(int id)
        {
            string sql = "select * from tb_ConsumeType where id = " + id;
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            Model.ConsumeType consumeType = EntityBase.DataTableToModel<Model.ConsumeType>(dt);
            return consumeType;
        }

        /// <summary>
        /// 返回所有的消费类型
        /// </summary>
        /// <returns>List</returns>
        public List<Model.ConsumeType> GetConsumeTypeList()
        {
            string sql = "select * from tb_ConsumeType";
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            List<Model.ConsumeType> list = EntityBase.DataTableToList<Model.ConsumeType>(dt);
            return list;
        }

        /// <summary>
        /// 消费类型分页
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="typeName">类型名称</param>
        /// <returns>Paging</returns>
        public Paging<Model.ConsumeType> GetConsumeTypePaging(int pageIndex, int pageSize, string typeName)
        {
            #region 查询条件

            StringBuilder builder = new StringBuilder();
            if (!string.IsNullOrWhiteSpace(typeName))
            {
                builder.AppendFormat(" and ct_TypeName like '%{0}%'", typeName);
            }

            #endregion
            Paging<Model.ConsumeType> _paging = new Paging<Model.ConsumeType>(pageIndex, pageSize, "tb_ConsumeType", builder.ToString());
            _paging.GetPageingDataList();
            #region 获取上级名称
            if (_paging != null && _paging.DataList.Count > 0)
            {
                for (int i = 0; i < _paging.DataList.Count; i++)
                {
                    Model.ConsumeType _model = GetConsumeTypeModel(_paging.DataList[i].ct_ParentId.ToInt32());
                    if (_model != null && _model.id > 0)
                    {
                        _paging.DataList[i].ParenetName = _model.ct_TypeName;
                    }
                    else if (_paging.DataList[i].ct_ParentId.ToInt32() == 0)
                    {
                        _paging.DataList[i].ParenetName = "顶级分类";
                    }
                    else
                    {
                        _paging.DataList[i].ParenetName = "-";
                    }
                }
            }
            #endregion
            return _paging;
        }

        /// <summary>
        /// 删除消费类型
        /// </summary>
        /// <param name="ids">主键集合</param>
        /// <returns>受影响的行数</returns>
        public int DeleteConsumeType(string ids)
        {
            string sql = "delete from tb_ConsumeType where id in(" + ids + ")";
            int count = DBHelper.SQLHepler.ExecuteSql(sql);
            return count;
        }

        #endregion

        #region 消费

        /// <summary>
        /// 添加消费类型
        /// </summary>
        /// <param name="consume">Consume</param>
        /// <returns>受影响的行数</returns>
        public int AEConsume(Model.Consume consume)
        {
            #region Insert语句
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("INSERT INTO [dbo].[tb_Consume]");
            sql.AppendFormat("([c_Name]");
            sql.AppendFormat(",[c_CTypeId]");
            sql.AppendFormat(",[c_UserId]");
            sql.AppendFormat(",[c_CreateUserId]");
            sql.AppendFormat(",[c_TotalPrice]");
            sql.AppendFormat(",[c_ActualPayment]");
            sql.AppendFormat(",[c_PersonTotalCount]");
            sql.AppendFormat(",[c_SettlementStatus]");
            sql.AppendFormat(",[c_Remark]");
            sql.AppendFormat(",[c_CTime]");
            sql.AppendFormat(",[c_InputPerson]");
            sql.AppendFormat(",[c_InputTime])");
            sql.AppendFormat(" VALUES ");
            sql.AppendFormat("('{0}'", consume.c_Name);
            sql.AppendFormat(",{0}", consume.c_CTypeId);
            sql.AppendFormat(",{0}", consume.c_UserId);
            sql.AppendFormat(",{0}", consume.c_CreateUserId);
            sql.AppendFormat(",{0}", consume.c_TotalPrice);
            sql.AppendFormat(",{0}", consume.c_ActualPayment);
            sql.AppendFormat(",{0}", consume.c_PersonTotalCount);
            sql.AppendFormat(",'{0}'", consume.c_SettlementStatus);
            sql.AppendFormat(",'{0}'", consume.c_Remark);
            sql.AppendFormat(",'{0}'", consume.c_CTime);
            sql.AppendFormat(",'{0}'", consume.c_InputPerson);
            sql.AppendFormat(",'{0}')", consume.c_InputTime);
            #endregion

            int count = DBHelper.SQLHepler.ExecuteSql(sql.ToString());
            return count;
        }

        /// <summary>
        /// 获取分页信息
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="rows">页大小</param>
        /// <param name="c_Name">名称</param>
        /// <param name="c_SettlementStatus">结算状态</param>
        /// <param name="c_UserId">消费人</param>
        /// <param name="c_CTypeId">类型</param>
        /// <returns>Paging</returns>
        public Paging<Model.Consume> GetConsumePaging(int pageIndex, int pageSize, string c_Name, int? c_SettlementStatus, int c_UserId, int c_CTypeId)
        {
            //查询条件
            string where = string.Empty;
            if (!string.IsNullOrWhiteSpace(c_Name))
            {
                where += " and c_Name like '%" + c_Name + "%'";
            }

            //2019-9-7 15:08:09 王浩力 添加
            if (!c_SettlementStatus.HasValue)
            {
                where += " and c_SettlementStatus = " + (int)ConsumeSys.Enum.ConsumeEnum.SettlementStatus.未结算;
            }
            else if (c_SettlementStatus != null)
            {
                where += " and c_SettlementStatus = " + c_SettlementStatus;
            }

            if (c_UserId > 0)
            {
                where += " and c_UserId =" + c_UserId;
            }
            if (c_CTypeId > 0)
            {
                where += " and c_CTypeId =" + c_CTypeId;
            }
            Paging<Model.Consume> _paging = new Paging<Model.Consume>(pageIndex, pageSize, "tb_Consume", where);
            _paging.GetPageingDataList();
            #region 获取外键名称
            if (_paging != null && _paging.DataList.Count > 0)
            {
                for (int i = 0; i < _paging.DataList.Count; i++)
                {
                    _paging.DataList[i].RealName = userDAL.GetUser(_paging.DataList[i].c_UserId).u_RealName;
                    _paging.DataList[i].TypeName = this.GetConsumeTypeModel(_paging.DataList[i].c_CTypeId).ct_TypeName;
                }
            }
            #endregion
            return _paging;
        }

        /// <summary>
        /// 删除消费
        /// </summary>
        /// <param name="ids">主键集合</param>
        /// <returns>受影响的行数</returns>
        public int DeleteConsume(string ids)
        {
            string sql = "delete from tb_Consume where id in(" + ids + ")";
            int count = DBHelper.SQLHepler.ExecuteSql(sql);
            return count;
        }

        #endregion

        /// <summary>
        /// 计算未结算的消费记录
        /// </summary>
        /// <returns></returns>
        public DataTable JiSuan(int jisuanED, int weiJiSuan)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM [v_JieSuanTable]";
                dt = DBHelper.SQLHepler.GetDataTable(sql);
                if (dt.Rows.Count > 0)
                {
                    //==========执行事务===========
                    //修改记录结算状态            
                    string sql2 = "update [tb_Consume] set [c_SettlementStatus]=" + jisuanED + "  where [c_SettlementStatus]=" + weiJiSuan;
                    //添加记录到计算结果表
                    string json = dt.ToJson();
                    string sql3 = "INSERT INTO [dbo].[JieSuanResult] ([result],[createTime]) VALUES (@result,@createTime)";
                    SqlConnection connection = null;
                    SqlTransaction transaction = null;
                    try
                    {
                        string conTxt = DBHelper.SQLHepler.connStr;
                        connection = new SqlConnection(conTxt);
                        connection.Open();
                        transaction = connection.BeginTransaction();

                        SqlCommand cmd1 = new SqlCommand(sql2, connection);
                        SqlCommand cmd2 = new SqlCommand(sql3, connection);

                        cmd1.Transaction = transaction;
                        cmd2.Transaction = transaction;

                        cmd2.Parameters.Add(new SqlParameter("@result", json));
                        cmd2.Parameters.Add(new SqlParameter("@createTime", DateTime.Now));
                        //cmd2.Parameters.Add("@result", json);
                        //cmd2.Parameters.Add("@createTime", DateTime.Now);

                        cmd1.ExecuteNonQuery();
                        cmd2.ExecuteNonQuery();

                        transaction.Commit();

                        cmd1.Dispose();
                        cmd2.Dispose();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                    }
                    finally
                    {
                        if (connection != null)
                        {
                            if (connection.State != ConnectionState.Closed)
                            {
                                connection.Close();
                                connection.Dispose();
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 查询往期结算记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetPageData()
        {
            string sql = @"SELECT  [id]
                                  ,[result]
                                  ,[createTime]
                              FROM [JieSuanResult]";
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            return dt;
        }

        /// <summary>
        /// 查询一条记录，根据主键
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        /// 2019-11-13 23:43:36 王浩力 添加
        public Consume Get(int id)
        {
            DataContext dataContext = new DataContext();
            var model = dataContext.Consume.AsNoTracking().FirstOrDefault(x => x.id == id);
            return model;
        }


    }
}
